/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sv.com.intesal.compra.sql;

/**
 *
 * @author JM
 */
public class SQLAjuste {

    //public static final String insertAjuste="INSERT INTO AJUSTE(codigo_s,id_dm,id_u,fecha_aj,fechahora,cantidad_aj,tipo_aj,descripcion_aj)VALUES(?,?,?,?,now(),?,?,?)";
    //public static final String findAjustes="SELECT * FROM AJUSTE as a,SUCURSAL as s,USUARIO AS u,MATERIAL_SUCURSAL AS ms,MATERIAL as m where m.id_m=ms.id_m and s.codigo_s=a.codigo_s and ms.id_dm=a.id_dm and u.id_u=a.id_u";
    //public static final String findAjustes_con_parametros="SELECT * FROM AJUSTE as a,SUCURSAL as s,USUARIO AS u,MATERIAL_SUCURSAL AS ms,MATERIAL as m where m.id_m=ms.id_m and s.codigo_s=a.codigo_s and ms.id_dm=a.id_dm and u.id_u=a.id_u and a.fecha_aj between ? and ?";
    public static final String getMax = "SELECT max(id_aj) as max FROM AJUSTE";

    public static final String insertEncaAjuaste = "INSERT INTO AJUSTE(codigo_s,id_u,fecha_aj,fechahora,descripcion_aj,bloque_aj,numero_documento_aj)VALUES(?,?,?,now(),?,?,?)";
    public static final String insertDetaAjuste = "INSERT INTO DETA_AJUSTE(id_dm,id_aj,cantidad_d,tipo_d)VALUES(?,?,?,?)";
    public static final String modificarEncaAjuste = "UPDATE AJUSTE SET fecha_aj=?,descripcion_aj=?,bloque_aj=?,numero_documento_aj=? where id_aj=?";

    public static final String eliminarEnca = "DELETE FROM AJUSTE where id_aj=?";
    public static final String eliminarDeta = "DELETE FROM DETA_AJUSTE where id_aj=?";
    public static final String eliminarDetaItem = "DELETE FROM DETA_AJUSTE where id_d=?";
    public static final String cambiarEstado="UPDATE AJUSTE set estado_aj=? where id_aj=?";

    /*Consulta de Ajuste*/
    public static final String getConsulta_DetaAjuste = "select d.id_d,d.id_aj,d.id_dm,d.tipo_d,d.cantidad_d,m.nombre_m FROM DETA_AJUSTE as d,MATERIAL_SUCURSAL as ms,MATERIAL as m where m.id_m=ms.id_m and ms.id_dm=d.id_dm and d.id_aj=? order by m.nombre_m";
    public static final String getConsulta_EncaAjuste="select aj.id_aj,aj.bloque_aj,aj.numero_documento_aj,aj.fecha_aj,aj.fechahora,aj.descripcion_aj,aj.estado_aj,u.id_u,u.usuario_u,s.codigo_s,s.nombre_s  FROM AJUSTE aj,SUCURSAL as s,USUARIO as u where u.id_u=aj.id_u and s.codigo_s=aj.codigo_s and aj.fecha_aj between ? and ? order by aj.fecha_aj,aj.numero_documento_aj";

    public static final String getConsulta_ReporteAjuste="";

    /*MODIFICAR EL AJUSTE*/
    public static final String getEncaAJ="select aj.id_aj,aj.bloque_aj,aj.numero_documento_aj,aj.fecha_aj,aj.fechahora,aj.descripcion_aj,aj.estado_aj,u.id_u,u.usuario_u,s.codigo_s,s.nombre_s  FROM AJUSTE aj,SUCURSAL as s,USUARIO as u where u.id_u=aj.id_u and s.codigo_s=aj.codigo_s and aj.id_aj=?";
    public static final String validarCantidadAnular="select m.nombre_m,d.id_dm,sum((d.cantidad_d) * ( CASE WHEN d.tipo_d='S' THEN 1 ELSE 1 END)) as suma  from deta_ajuste d ,MATERIAL_SUCURSAL as ms,MATERIAL as m where m.id_m=ms.id_m and ms.id_dm=d.id_dm and d.id_aj=? group by d.id_dm,m.nombre_m";


    /*
     REPORTE DE AJUSTE
        select aj.*,d.*,s.codigo_s,s.nombre_s,u.usuario_u from AJUSTE aj,DETA_AJUSTE d,SUCURSAL as s,USUARIO as u
        where s.codigo_s=aj.codigo_s and aj.id_aj=d.id_aj and u.id_u=aj.id_u order by s.nombre_s,aj.fecha_aj,aj.fechahora,aj.numero_documento_aj
     */
}
